#!/usr/bin/env python3
# encoding=utf-8

import with_win32 as wwin32
from threading import Lock, get_ident
from word_process import fill_word_with_excel_value
from commom_api import (get_excel_setting_info, is_weekend_day, check_code_in_list
                        , get_chart_rules, excel_rename_list_update)

import logging
logger = logging.getLogger(__name__)
img_lock = Lock()

def change_xlsinfo_by_win32_sheet(sheet):
    xlsinfo = {}
    xlsbase = get_excel_setting_info()
    maxcol = sheet.UsedRange.Columns.Count
    for col in range(1, maxcol + 1):
        cval = sheet.Cells(xlsbase['headerline'], col).Value
        # print(f'col{col} is {cval}')
        if cval == None:
            logger.debug(f"col max {maxcol} {col} none data")
            break
        for key, val in xlsbase.items():
            if cval == val:
                xlsinfo[key] = (col, val)
    for key, val in xlsbase.items():
        if key in xlsinfo:
            continue
        xlsinfo[key] = val
    
    maxrow = sheet.UsedRange.Rows.Count
    for row in range(0, maxrow):
        cval = sheet.Cells(maxrow - row, 1).Value
        # print(f'row{row} is {cval}')
        if cval != None:
            # logger.debug(f"row max {maxrow} {row} none data")
            break
    maxrow = maxrow - row
    xlsinfo['flexiblepos'] = [xlsbase['headerline']+1, col, maxrow]

    mindate = sheet.Cells(xlsbase['headerline']+1, 1).Value.replace(tzinfo=None)
    maxdate = sheet.Cells(maxrow, 1).Value.replace(tzinfo=None)
    xlsinfo['daterange'] = [mindate, maxdate]
    return xlsinfo

def copy_win32_cell_format_one_by_one(source_cell, target_cell):
    # 复制字体
    target_cell.Font.Name = source_cell.Font.Name
    target_cell.Font.Size = source_cell.Font.Size
    target_cell.Font.Bold = source_cell.Font.Bold
    target_cell.Font.Italic = source_cell.Font.Italic
    target_cell.Font.Color = source_cell.Font.Color

    # 复制填充
    target_cell.Interior.Color = source_cell.Interior.Color
    target_cell.Interior.Pattern = source_cell.Interior.Pattern

    # 复制边框
    # for border_id in range(1, 13):  # 12 种边框类型
    #     target_cell.Borders(border_id).LineStyle = source_cell.Borders(border_id).LineStyle
    #     target_cell.Borders(border_id).Weight = source_cell.Borders(border_id).Weight
    #     target_cell.Borders(border_id).Color = source_cell.Borders(border_id).Color

    # 复制对齐
    target_cell.HorizontalAlignment = source_cell.HorizontalAlignment
    target_cell.VerticalAlignment = source_cell.VerticalAlignment
    target_cell.WrapText = source_cell.WrapText

    # 复制数字格式
    target_cell.NumberFormat = source_cell.NumberFormat

def deal_with_win32_sheet_data(wb, xlsinfo, onemail, codeinfo):
    sheet = wb.Worksheets[xlsinfo['sheetname']]
    flexiblecol = xlsinfo['flexiblepos'][1] + 1
    lastrow = xlsinfo['flexiblepos'][2]

    for col in range(1, flexiblecol):
        onekey = None
        for key, val in xlsinfo.items():
            try:
                if col == val[0]:
                    onekey = key
            except:
                continue
        # logger.info(onekey)
        oldcell = sheet.Cells(lastrow, col)
        newcell = sheet.Cells(lastrow+1, col)
        if oldcell.HasFormula:
            cval = oldcell.Formula
            cval = cval.replace(str(lastrow), str(lastrow+1))
            cval = cval.replace(str(lastrow-1), str(lastrow))
            newcell.Formula = cval
        else:
            cval = oldcell.Value
            if onekey != None:
                # logger.info(onekey)
                if onekey in onemail.keys():
                    # logger.info('get from email')
                    if onekey == 'date':
                        # oneday = datetime.datetime.strptime(onemail[onekey], "%Y-%m-%d")
                        newcell.Value = onemail[onekey]    # 不能用字符串，必须用时间，不然count失效
                        # newcell.NumberFormat = 'yyyy/mm/dd'
                        if len(xlsinfo['daterange']) != 3:
                            xlsinfo['daterange'].append(onemail[onekey])
                        else:
                            xlsinfo['daterange'][2] = onemail[onekey]
                    else:
                        newcell.Value = float(onemail[onekey])
                elif onekey in codeinfo.keys():
                    # logger.info('get from code')
                    for date, val in codeinfo[onekey]:
                        if date == onemail['date']:
                            newcell.Value = val
            else:
                newcell.Value = cval
        #   多线程使用剪切板，很容易出异常
        # wwin32.win32_cell_paste(oldcell, newcell)  
        # 直接使用简单的格式赋值，最可靠   
        copy_win32_cell_format_one_by_one(oldcell, newcell)   

    cb2 = sheet.Range('B2').Formula.replace(str(lastrow), str(lastrow+1))
    sheet.Range('B2').Formula = cb2
    logger.info(f'{cb2} for %s' % onemail['date'])
    xlsinfo['flexiblepos'][2] = lastrow + 1
    # wb.save(xlsinfo['excel'])
    # wb.RefreshAll()                # 刷新数据透视表、Power Query等
    wb.Save()
    return xlsinfo

def deal_with_win32_sheet_oldchart(wb, xlsinfo):
    maxrow = xlsinfo['flexiblepos'][2]
    sheet = wb.Worksheets[xlsinfo['sheetname']]
    for chart in sheet.ChartObjects():
        # chart.Chart.ChartTitle.Text = '123456'
        # 更新折线数据
        for series in chart.Chart.SeriesCollection():   
            # print(series.val.numRef.f)
            series.Formula = series.Formula.replace(f'${maxrow-1}', f'${maxrow}')
        chart.Top = chart.Top + 15  # 新增行，位置同步下移15
    # wb.RefreshAll()                # 刷新数据透视表、Power Query等
    wb.Save()

def control_win32_chart_show(wb, chart, xlsinfo):
    if isinstance(xlsinfo, str):
        for series in chart.SeriesCollection():
            series.Format.Line.Transparency = 0
            series.IsFiltered = False  # 直接禁用标签显示
    else:
        if 'chartrules' not in xlsinfo:
            return
        rule = get_chart_rules(xlsinfo['chartrules'], xlsinfo['code'], xlsinfo['daterange'][0])
        for series in chart.SeriesCollection():
            if series.Name not in rule:
                continue
            series.Format.Line.Transparency = 1.0   #   完全透明 等于隐藏
            series.IsFiltered = True  # 直接禁用标签显示
    wb.RefreshAll()                # 刷新数据透视表、Power Query等
    # excel.CalculateUntilAsyncQueriesDone()  # 等待异步查询完成
    # wb.Save()

def get_img_from_win32_sheet(wb, xlsinfo):
    sheet = wb.Worksheets[xlsinfo['sheetname']]
    for idx, chart_obj in enumerate(sheet.ChartObjects()):
        chname = chart_obj.Chart.ChartTitle.Text
        pos = chname.find('超额')
        if pos > 0:
            continue
        logging.info(f'excel chart {chname}')
        # print(f'excel chart {chname}') # 获取目标系列
        control_win32_chart_show(wb, chart_obj.Chart, xlsinfo)
        pngname = xlsinfo['excel'].replace('.xlsx', '.png')
        with img_lock:  # 加锁
            chart_obj.Chart.Export(pngname)
        logging.info(f'excel chart {chname} sucess')
        # control_win32_chart_show(wb, chart_obj.Chart, 'showall')

        # 生成唯一文件名：工作表名_图表索引.png
        # filename = f"{sheet.Name}_chart_{idx+1}.jpg"
        # image_path = os.path.join(os.path.dirname(xlsinfo['excel']), filename)
        # chart_obj.Chart.Export(image_path) # 导出图表

def win32_with_word_excel(wb, xlsinfo):
    if hasattr(wb, 'Worksheets'):
        # get_img_from_win32_sheet(wb, xlsinfo)
        fill_word_with_excel_value(wb, xlsinfo)
        return wb
    try:
        with wwin32.Win32AppManager(xlsinfo['excel']) as (excel, wb):
            # print(wb.Sheets(1).name)
            for sheet in wb.Sheets:
                # print(sheet.Name)
                if not sheet.Name.startswith(xlsinfo['sheetname']):
                    continue
                # get_img_from_win32_sheet(wb, xlsinfo)
                fill_word_with_excel_value(wb, xlsinfo)
    except Exception as e:
        logging.error(f"myexcel chart error[{e}] {xlsinfo['sheetname']}")
        wwin32.kill_excel_process()  # 兜底清理
    return wb

def force_updae_win32_excel_value(excel, wb):
    excel.Calculation = -4105  # xlCalculationAutomatic (设置为自动计算模式)
    # 方法2：仅计算当前工作簿（更高效）
    wb.RefreshAll()                # 刷新数据透视表、Power Query等
    # wb.UpdateLink()               # 更新外部数据链接
    excel.CalculateUntilAsyncQueriesDone()  # 等待异步查询完成
    # 保存计算结果
    wb.Save()

def duil_with_file_by_win32(excelpath, maillist, codeinfo):
    renamefile = {}
    try:
        # 打开工作簿，并固定工作本顺顺序，只关注日和周
        with wwin32.Win32AppManager(excelpath) as (excel, wb):
            for sheet in wb.Worksheets:
                xlsinfo = change_xlsinfo_by_win32_sheet(sheet)
                xlsinfo['excel'] = excelpath
                xlsinfo['sheetname'] = sheet.Name
                logger.info(f"{sheet.Name}")
                if sheet.Name.find('（日）') > 0:
                    for one in maillist:
                        xlsinfo['code'] = one['code']
                        xlsinfo = deal_with_win32_sheet_data(wb, xlsinfo, one, codeinfo)
                        sheet.Visible = False
                        deal_with_win32_sheet_oldchart(wb, xlsinfo)
                elif sheet.Name.find('（周）') > 0:
                    for one in maillist:
                        xlsinfo['code'] = one['code']
                        if is_weekend_day(one['date']) == False:
                            continue
                        xlsinfo = deal_with_win32_sheet_data(wb, xlsinfo, one, codeinfo)
                        deal_with_win32_sheet_oldchart(wb, xlsinfo)
                        # force_updae_win32_excel_value(excel, wb)
                        win32_with_word_excel(wb, xlsinfo)
                    else:
                        continue
                renamefile = excel_rename_list_update(xlsinfo, renamefile)
                        
    except Exception as e:
        logging.error(f"myexcel {excelpath} fill error[{e}]")
        wwin32.kill_excel_process()  # 兜底清理  
        return renamefile 
    return renamefile
